"""
This file will have basic utility functions
"""
from os import listdir
import MySQLdb
import re, urllib2
from collections import Counter
#"""
site_file = "temp_sites"#"all_site_names" #44_sites
folder_path = "D:\Research\ISEC-2014\data\content"
user = "root"
password="123"
database = "isec2014"
port=3306
"""
site_file = "all_site_name" #44_sites
#E:\Sangeeta\dataset\2013_05_stack_overflow\Content\meta.bicycles.stackexchange.com
folder_path = "E:\Sangeeta\dataset\\2013_05_stack_overflow\Content"
user = "sangeetal"
password="sangeetal"
database = "sangeeta"
port=3307
#"""

db1= MySQLdb.connect(host="localhost", user=user, passwd=password,db=database, port=port)

def read_and_store_sites():
    """called only once"""
    folder = listdir(folder_path)
    file = open("site_names",'w')
    for f in folder:
        print f
        index = f.rfind(".")
        print index
        f= f[0:index]
        file.write(f)
        file.write('\n')

    file.close()  
     
"""This function will create tables posthistory"""
def make_posthistory_tables():
    file = open(site_file)
    site_names = list()
    for f in file:
        site_names.append(f)
        
    for site in site_names:
        print "site=", site
        table ="posthistory_"+((str) (site)).strip()+"_aug13"
        create_str = "CREATE TABLE `" +table+"` ("\
        "`id` int(11) NOT NULL DEFAULT '0',"+\
        "`posthistorytypeid` int(11) DEFAULT NULL,"+\
        "`postid` int(11) DEFAULT NULL,"+\
        "`revisionguid` varchar(40) DEFAULT NULL,"+\
        "`creationdate` datetime DEFAULT NULL,"+\
        "`userid` varchar(30) DEFAULT NULL,"+\
        "`user_display_name` varchar(100) DEFAULT NULL,"+\
        "`comment` text,"+\
        "`text` text,"+\
        "PRIMARY KEY (`id`)"+\
        ") ENGINE=InnoDB DEFAULT CHARSET=latin1;"
        print "create_str=", create_str
        cur = db1.cursor()
        cur.execute(create_str)
    
"""This will  create posts table"""
def make_posts_tables():
    file = open(site_file)
    site_names = list()
    for f in file:
        site_names.append(f)
        
    for site in site_names:
        print "site=", site
        table ="posts_"+((str) (site)).strip()+"_aug13"
        create_str = "CREATE TABLE `" +table+"` ("\
        "`id` int(11) NOT NULL DEFAULT '0',"+\
        "`posttypeid` int(11) DEFAULT NULL,"+\
        "`parentid` int(11) DEFAULT NULL,"+\
        "`accepted_answer_id` int(11) DEFAULT NULL,"+\
        "`creation_date` datetime DEFAULT NULL,"+\
        "`score` int(11) DEFAULT NULL,"+\
        "`view_count` int(11) DEFAULT NULL,"+\
        "`body` longtext,"+\
        "`owner_user_id` int(11) DEFAULT NULL,"+\
        "`owner_display_name` varchar(100) DEFAULT NULL,"+\
        "`last_editor_used_id` int(11) DEFAULT NULL,"+\
        "`last_edit_date` datetime DEFAULT NULL,"+\
        "`last_activity_date` datetime DEFAULT NULL,"+\
        "`title` text,"+\
        "`tags` varchar(500) DEFAULT NULL,"+\
        "`answer_count` int(11) DEFAULT NULL,"+\
        "`comment_count` int(11) DEFAULT NULL,"+\
        "`community_owned_date` datetime DEFAULT NULL,"+\
        "PRIMARY KEY (`id`)"+\
        ") ENGINE=InnoDB DEFAULT CHARSET=latin1;"

        print "create_str=", create_str
        cur = db1.cursor()
        cur.execute(create_str)
        
        
        
def make_users_tables():
    file = open(site_file)
    site_names = list()
    for f in file:
        site_names.append(f)
        
    for site in site_names:
        print "site=", site
        table ="users_"+((str) (site)).strip()+"_aug13"
        create_str = "CREATE TABLE `" +table+"` ("\
        "`id` int(11) NOT NULL DEFAULT '0',"\
        "`reputation` int(11) DEFAULT NULL,"\
        "`creation_date` datetime DEFAULT NULL,"\
        "`display_name` varchar(100) DEFAULT NULL,"\
        "`last_access_date` datetime DEFAULT NULL,"\
        "`web_url` text,"\
        "`about_me` text,"\
        "`location` varchar(1000) DEFAULT NULL,"\
        "`views` int(11) DEFAULT NULL,"\
        "`upvotes` int(11) DEFAULT NULL,"\
        "`down_votes` int(11) DEFAULT NULL,"\
        "`email_hash` varchar(100) DEFAULT NULL,"\
        "`age` int(11) DEFAULT NULL,"\
        "PRIMARY KEY (`id`)"\
        ") ENGINE=InnoDB DEFAULT CHARSET=latin1;"

        
        
        print "create_str=", create_str
        cur = db1.cursor()
        cur.execute(create_str)        
#read_and_store_sites()
#make_posthistory_tables()
#make_posts_tables()
#make_users_tables()

def get_tags_and_tagcount(tag_str):
    number_of_tags = len(tag_str.split(">"))-1
    temp_tags = tag_str.split(">")
    tags = list()
    
    count = 1
    for t in temp_tags:
        if count<=number_of_tags:
            tags.append(t.split("<")[1])
            count = count+1
        
   # print "tags=", tags
    #count = 2
    return number_of_tags, tags
    
    
def clean(val):
        return val



def  get_URL_count(body_str):
    url_count = 0
    return url_count
  
  
  

   
def get_links(content):
    links = re.findall(r'(https?://\S+)', content, re.I)
    
    print len(links)
    return links
    

def  get_First_Capital_Count(body_str):
    words = re.split('\s+', body_str)
    print words
    first_cap = list()
    for w in words:
        if w:
            if w[0].isupper():
                first_cap.append(w)
    print len(first_cap)
    
    
def  get_All_Capital_Count(body_str):
    words = re.split('\s+', body_str)
    #print words
    all_cap = list()
    for w in words:
        if w:
            if w.isupper():
                all_cap.append(w)
                
    print len(all_cap)    
    
tag_str = "<abc><hello><a>"
 
val, c = get_tags_and_tagcount(tag_str)
print val , c 
def body_have_code(body):
    try:
        body.index("<code>")
        return 1
    except ValueError:
        return 0

def count_of_special_chars(body):
    counter = Counter(body)   
    star = counter['*']
    dot  = counter['.']
    plus  =counter['+']
    open_brac =counter['[']
    close_brac = counter[']']
    for_slash = counter['/']
    back_slash = counter['\\']
    under_score =counter['_']
    hyphens  =counter['-']
    
    #print  "star = ", star 
    return star+dot+plus+open_brac+close_brac+for_slash+back_slash+under_score+hyphens
 
site = ['a','b']
for s in site:
    print s   
get_links("http://hi/   fhghg  http://hmm http://hmmm jjj")
get_First_Capital_Count("fgfhf      Hmm Juk ")
get_All_Capital_Count("HELLO+ hi Hi Hmm hhjj     kkk")

body = "hehue <co]*]]]] [hello.com\\"
print "have code=", body_have_code(body)
print "start", count_of_special_chars(body)